<?php
// Authenticate
require("class.AuthenticationManager.php");
require("class.CommandMenu.php");
if (!$authenticationManager->isLoggedIn() || !$authenticationManager->hasClearance(CLEARANCE_ADMINISTRATOR)) {
    Header("Location: login.php?redirect=$_SERVER[PHP_SELF]&clearanceRequired=Administrator");
    exit;
}

// Connect to database:
$dbh = dbConnect();

// Load local vars from superglobals:
if (isset($_REQUEST['month']))
{    
    $month = $_REQUEST['month'];
}
if (isset($_REQUEST['year']))
{    
    $year = $_REQUEST['year'];
}
if (isset($_REQUEST['mode']))
{    
    $mode = $_REQUEST['mode'];
}
if (isset($_REQUEST['query']))
{    
    $query = $_REQUEST['query'];
}

  
// Calculate the previous month.
$next_month = $month + 1;
$next_year  = $year;
$prev_month = $month - 1;
$prev_year  = $year;

//rollover year forward
if (!checkdate($next_month, 1, $next_year)) {
	$next_month -= 12;
	$next_year ++;
}

//rollover year back
if (!checkdate($prev_month, 1, $prev_year)) {
    $prev_month += 12;
    $prev_year --;
}


//run the query  
list($qh,$num) = dbQuery($query);

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Insert Silkan Logo:
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('PHPExcel logo');
$objDrawing->setDescription('PHPExcel logo');
$objDrawing->setPath('./images/silkan.gif');
$objDrawing->setHeight(60);
$objDrawing->setCoordinates('B2');
//$objDrawing->setOffsetX(-10);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

// Merge Cells
$objPHPExcel->getActiveSheet()->mergeCells('B2:B4');

// Set document properties
$objPHPExcel->getProperties()->setCreator("TimeSheet.php")
							 ->setLastModifiedBy("TimeSheet.php")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");


//first_name, last_name, $USER_TABLE.username, $PROJECT_TABLE.title, $PROJECT_TABLE.proj_id, $TASK_TABLE.name, $TASK_TABLE.task_id ".

$row = 10;
if ($num == 0) {
    // Add some data
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'No data !');
} else {
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('B10', 'Client')
                ->setCellValue('C10', 'Project title')
                ->setCellValue('D10', 'Project leader')
				->setCellValue('E10', 'Company')
                ->setCellValue('F10', 'Task name')
				->setCellValue('G10', 'Task manager')
				->setCellValue('H10', 'Related RnD Project');

    $row++;
	while ($data = dbResult($qh)) 
	{

	    
		$client         = utf8_encode(stripslashes($data["organisation"]));
		$project_title  = utf8_encode(stripslashes($data["title"]));
		$project_leader = utf8_encode(stripslashes($data["proj_leader"]));
	    $company        = utf8_encode(stripslashes($data['company']));
		$task_name      = utf8_encode(stripslashes($data["name"]));
		$task_manager   = utf8_encode(stripslashes($data["manager"]));
		$source_project = utf8_encode(stripslashes($data["source_project"]));
		$target_project = utf8_encode(stripslashes($data["target_project"]));
		
		if($source_project == $target_project) 
		{
		    $Related_RnD_Project = "";
		} else {
			$Related_RnD_Project = $source_project;
		}
		

		// Add some data
		$str = 'B'.$row.'';
		$objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueByColumnAndRow(1, $row, $client);
		$objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		
		

		$str = 'C'.$row.'';
        $objPHPExcel->setActiveSheetIndex(0)
				   ->setCellValueByColumnAndRow(2, $row, $project_title);
		$objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
			

		$str = 'D'.$row.'';
        $objPHPExcel->setActiveSheetIndex(0)			
					->setCellValueByColumnAndRow(3, $row, $project_leader);
		$objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));		

		$str = 'E'.$row.'';
        $objPHPExcel->setActiveSheetIndex(0)			
					->setCellValueByColumnAndRow(4, $row, $company);
		$objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));

		$str = 'F'.$row.'';	
        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValueByColumnAndRow(5, $row, $task_name);
		$objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
			
	    $str = 'G'.$row.'';
        $objPHPExcel->setActiveSheetIndex(0)
					->setCellValueByColumnAndRow(6, $row, $task_manager);
		$objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));	    
			
		$str = 'H'.$row.'';
        $objPHPExcel->setActiveSheetIndex(0)
					->setCellValueByColumnAndRow(7, $row, $Related_RnD_Project);
		$objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));	    
			
			
	    $row++;
	}
}

// Set fonts

			
$objPHPExcel->getActiveSheet()->getStyle('B10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('B10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '2B5714'))));
$objPHPExcel->getActiveSheet()->getStyle('B10')->applyFromArray( 
            array('fill'       => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID   , 'color' => array('rgb' => 'BCCF00'))));
			
$objPHPExcel->getActiveSheet()->getStyle('C10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('C10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('C10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('C10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('D10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('D10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('D10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('D10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('E10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('E10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));			

$objPHPExcel->getActiveSheet()->getStyle('F10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('F10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('F10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));

$objPHPExcel->getActiveSheet()->getStyle('G10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('G10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('G10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('G10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
		
$objPHPExcel->getActiveSheet()->getStyle('H10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('H10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('H10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('H10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));


			
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(40);

				
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Report_by_all_projects');


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Report_by_all_projects_and_tasks.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
